import requests
import time
import xlrd
import openpyxl
from openpyxl import Workbook
from typing import List, Dict, Tuple

class AddressToCoordinates:
    def __init__(self, api_key: str):
        """
        初始化地址转经纬度转换器
        :param api_key: 高德地图API密钥
        """
        self.api_key = api_key
        self.base_url = "https://restapi.amap.com/v3/geocode/geo"
        
    def get_coordinates(self, address: str) -> Tuple[float, float, str]:
        """
        获取单个地址的经纬度
        :param address: 地址字符串
        :return: (经度, 纬度, 格式化地址)
        """
        params = {
            "key": self.api_key,
            "address": address,
            "output": "json"
        }
        
        try:
            print(f"正在处理地址: {address}")
            response = requests.get(self.base_url, params=params)
            data = response.json()
            
            print(f"API响应: {data}")
            
            if data["status"] == "1" and data["geocodes"]:
                location = data["geocodes"][0]["location"]
                lng, lat = map(float, location.split(","))
                formatted_address = data["geocodes"][0]["formatted_address"]
                print(f"成功获取坐标: 经度={lng}, 纬度={lat}")
                return lng, lat, formatted_address
            else:
                print(f"API返回错误: {data.get('info', '未知错误')}")
                return None, None, None
                
        except Exception as e:
            print(f"处理地址 '{address}' 时发生错误: {str(e)}")
            return None, None, None

    def batch_convert(self, addresses: List[str], output_file: str = "coordinates_result.xlsx"):
        """
        批量转换地址为经纬度
        :param addresses: 地址列表
        :param output_file: 输出文件名
        """
        results = []
        
        for address in addresses:
            lng, lat, formatted_address = self.get_coordinates(address)
            results.append({
                "原始地址": address,
                "格式化地址": formatted_address or "",
                "经度": lng or "",
                "纬度": lat or ""
            })
            # 添加延时以避免API限制
            time.sleep(0.1)
        
        # 将结果保存到xlsx文件
        wb = Workbook()
        ws = wb.active
        ws.append(["原始地址", "格式化地址", "经度", "纬度"])
        for result in results:
            ws.append([result["原始地址"], result["格式化地址"], result["经度"], result["纬度"]])
        wb.save(output_file)
        print(f"结果已保存到 {output_file}")

def read_xls_column(file_path: str, column_index: int) -> List[str]:
    """
    读取xls文件指定列
    :param file_path: xls文件路径
    :param column_index: 列索引（从0开始）
    :return: 列数据列表
    """
    workbook = xlrd.open_workbook(file_path)
    sheet = workbook.sheet_by_index(0)
    return [str(sheet.cell_value(i, column_index)).strip() for i in range(1, sheet.nrows)]

def main():
    # 创建转换器实例
    converter = AddressToCoordinates("API_KEY")
    
    # 读取xls文件C列（索引为2）
    xls_file = "输入你的文件名称.xls"
    addresses = read_xls_column(xls_file, 2)
    
    # 执行批量转换
    converter.batch_convert(addresses)

if __name__ == "__main__":
    main() 